﻿using MySql.Data.MySqlClient;
using System;
using System.Data;

namespace Rail.Commons
{
    public class MySqlHelper
    {
        //public static string ConnStr = @"server=数据库;uid=帐号;pwd=密码;database=数据库;charset=utf8";
        public static string ConnStr { 
            get 
            {
                var dbIP = SerializeHelper.IniHelper.GetVal("DB","ip");
                var dbName = SerializeHelper.IniHelper.GetVal("DB", "dbName");
                var dbUser = SerializeHelper.IniHelper.GetVal("DB", "dbUser");
                var pwd = SerializeHelper.IniHelper.GetVal("DB", "pwd");
                return "server="+ dbIP + ";database=" + dbName + ";uid="+ dbUser + ";pwd="+ pwd + ";charset=utf8;";
            } 
        }
        //"server=localhost;database=rail;uid=root;pwd=123456;charset=utf8;";

        //打开数据库链接
        public static MySqlConnection OpenConn()
        {
            MySqlConnection Conn = new MySqlConnection(ConnStr);
            Conn.Open();
            return Conn;
        }
        //关闭数据库链接
        public static void CloseConn(MySqlConnection Conn)
        {
            if (Conn != null)
            {
                Conn.Close();
                Conn.Dispose();
            }
            GC.Collect();
        }
        //运行MySql语句
        public static int RunSQL(string SQL)
        {
            MySqlConnection Conn = OpenConn();
            MySqlCommand Cmd = CreateCmd(SQL, Conn);
            
            try
            {
                int result_count = Cmd.ExecuteNonQuery();

                CloseConn(Conn);
                return result_count;
            }
            catch
            {
                CloseConn(Conn);
                return 0;
            }
        }
        // 生成Command对象 
        public static MySqlCommand CreateCmd(string SQL, MySqlConnection Conn)
        {
            MySqlCommand Cmd = new MySqlCommand(SQL, Conn);
            return Cmd;
        }
        // 运行MySql语句返回 DataTable
        public static DataTable GetDataTable(string SQL, string Table_name)
        {
            MySqlDataAdapter Da = GetAdapter(SQL);
            DataTable dt = new DataTable(Table_name);
            Da.Fill(dt);
            return dt;
        }
        // 运行MySql语句返回 MySqlDataReader对象
        public static MySqlDataReader GetReader(string SQL)
        {
            MySqlConnection Conn = OpenConn();
            MySqlCommand Cmd = CreateCmd(SQL, Conn);
            MySqlDataReader Dr;
            try
            {
                Dr = Cmd.ExecuteReader(CommandBehavior.Default);
            }
            catch
            {
                throw new Exception(SQL);
            }
            CloseConn(Conn);
            return Dr;
        }
        // 运行MySql语句返回 MySqlDataAdapter对象 
        public static MySqlDataAdapter GetAdapter(string SQL)
        {
            MySqlConnection Conn = OpenConn();
            MySqlDataAdapter Da = new MySqlDataAdapter(SQL, Conn);
            return Da;
        }
        // 运行MySql语句,返回DataSet对象
        public static DataSet GetDataSet(string SQL, DataSet Ds)
        {
            MySqlDataAdapter Da = GetAdapter(SQL);
            try
            {
                Da.Fill(Ds);
            }
            catch (Exception Err)
            {
                throw Err;
            }
            return Ds;
        }
        // 运行MySql语句,返回DataSet对象
        public static DataSet GetDataSet(string SQL, DataSet Ds, string tablename)
        {
            MySqlDataAdapter Da = GetAdapter(SQL);
            try
            {
                Da.Fill(Ds, tablename);
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
            return Ds;
        }
        // 运行MySql语句,返回DataSet对象，将数据进行了分页
        public static DataSet GetDataSet(string SQL,  DataSet Ds, int StartIndex, int PageSize, string tablename)
        {
            MySqlConnection Conn = OpenConn();
            MySqlDataAdapter Da = GetAdapter(SQL);
            try
            {
                Da.Fill(Ds, StartIndex, PageSize, tablename);
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
            CloseConn(Conn);
            return Ds;
        }
        // 返回MySql语句执行结果的第一行第一列
        public static string Get_Row1_Col1_Value(string SQL)
        {
            MySqlConnection Conn = OpenConn();
            string result;
            MySqlDataReader Dr;
            try
            {
                Dr = CreateCmd(SQL, Conn).ExecuteReader();
                if (Dr.Read())
                {
                    result = Dr[0].ToString();
                    Dr.Close();
                }
                else
                {
                    result = "";
                    Dr.Close();
                }
            }
            catch
            {
                throw new Exception(SQL);
            }
            CloseConn(Conn);
            return result;
        }
        //Sql语句
        #region 
        /// <summary>
        /// SQL语句   指定表所有字段
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static string GetAllField(string tableName)
        {
            string sql = "select * from " + tableName;
            return sql;
        }
        /// <summary>
        /// SQL语句   给指定表添加一行数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public static string WriteTableRow(string tableName,object[] values)
        {
            string data="";
            for (int i = 0; i < values.Length; i++) 
            {
                data += i < values.Length-1 ? (values[i] + "','") : values[i];
            }
            string InsertSql = "insert into " + tableName + " values ('" +data + "')";
            return InsertSql;
        }
        #endregion
    }
}
